package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.TableDefinitionV2Dao;
import com.swordget.dao.TableManagerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.swordget.commons.ColumnType.NUMBER;
import static com.swordget.commons.ColumnType.VARCHAR2;
//import static com.swordget.commons.SimpleFinalUtil.TMP_SUFFIX;

/**
 * @Author: David
 * @Date: Created in 16:00 2020/3/27
 * @Description: 表结构管理 oracle 实现
 * @Modified By:
 * @Version:
 */
public class TableManagerOracleDao implements TableManagerDao {

    @Autowired
    private JdbcUtil jdbcUtil;

    @Autowired
    private DruidDataSource dataSource;

    /**
     * TableManagerOracleDao log
     */
    private final static Logger LOG = LoggerFactory.getLogger(TableManagerOracleDao.class);

    @Autowired
    private TableDefinitionV2Dao tableDefinitionV2Dao;

    /**
     * @param tableName 表名
     * @param userName  用户名
     * @return
     */
    @Override
    public boolean dataTableExists(String tableName, String userName) {
        String userExistsSQL = String.format("SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = UPPER('%s') " +
                "AND TABLE_NAME = '%s'", userName, tableName);
        int result = jdbcUtil.executeCount(userExistsSQL);
        return result > 0;
    }

    /**
     * @param username 用户名
     * @return
     */
    @Override
    public String getTableSpace(String username) {
        String getSpaceSql = String.format("SELECT default_tablespace FROM " +
                "DBA_USERS WHERE username ='%s'", username);
        return jdbcUtil.findOne(getSpaceSql).toString();
    }

    /**
     * @param tableDesign   表结构
     * @param columnDesigns 字段结构
     * @param serial        主键结构
     * @param tableSpace    表空间
     * @param username      用户名
     * @param loginId       操作用户
     * @return
     */
    @Override
    public boolean createTable(TableDesign tableDesign, List<ColumnDesign> columnDesigns,
                               ColumnDesign serial, String tableSpace, String username, String loginId) {
        StringBuilder tableBuilder = new StringBuilder();
        List<String> commentList = new ArrayList<>();
        String tableName = tableDesign.getTableEN();
        //tableBuilder.append(String.format("CREATE TABLE %s.%s(", username, tableDesign.getTableEN()));
        tableBuilder.append(String.format("CREATE TABLE %s(", tableDesign.getTableEN()));
        for (int i = 0; i < columnDesigns.size(); i++) {
            ColumnDesign columnDesign = columnDesigns.get(i);
            String name = columnDesign.getName().toUpperCase();
            String type = columnDesign.getType();
            String comment = columnDesign.getComment();

            tableBuilder.append(String.format("%s ", name));
            //判断类型添加字段长度
            if (VARCHAR2.equals(type)) {
                tableBuilder.append(String.format("VARCHAR2(%s) ", columnDesign.getWidth()));
            } else if (NUMBER.equals(type)) {
                tableBuilder.append(String.format("NUMBER(%s,%s) ", columnDesign.getWidth(), columnDesign.getPoint()));
            }
            //是否允许为空,添加了索引的字段默认不能为空
            if ("Y".equals(columnDesign.getNotNull())) {
                tableBuilder.append("NOT NULL ");
            }
            //最后一个字段不加逗号
            if (i != columnDesigns.size() - 1) {
                tableBuilder.append(",");
            }
            //添加注释
            commentList.add(String.format("COMMENT ON COLUMN %s.%s.%s is '%s'", username,
                    tableName, name, comment));
        }
        //继续生成表空间语句
        tableBuilder.append(String.format(") TABLESPACE %s ", tableSpace));
        System.out.println(tableBuilder.toString());
        //添加唯一索引
        String indexNum = String.format("SYS_C%08d", Math.round(Math.random() * 100000000));
        commentList.add(String.format("ALTER TABLE %s.%s ADD " +
                "CONSTRAINT %s UNIQUE(%s)", username, tableDesign.getTableEN(), indexNum, serial.getName()));
        try {
            //执行创建表语句
            boolean isCreate = jdbcUtil.execute(tableBuilder.toString());
            if (isCreate) {
                //执行注释语句
                return jdbcUtil.executeBatch(commentList);
            }
        } catch (Exception e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return false;
    }

    @Override
    public boolean editTable(TableDesign tableDesign, String loginId) {
        String editTableSql = String.format("UPDATE TABLEDESIGN SET REMARKS = '%s', UPLOADFREQUENCY = '%s'," +
                        " REPORTSTATUS = '%s', DATASOURCEID = '%S' WHERE TABLENAME = '%s' AND TABLENCOM = '%s'",
                tableDesign.getRemarks(), tableDesign.getUploadFrequency(), tableDesign.getReportStatus(),
                tableDesign.getDataSourceId(), tableDesign.getTableEN(), tableDesign.getTableCN());
        return jdbcUtil.executeUpdate(editTableSql);
    }

    /**
     * @param tableName 表名
     */
    @Override
    public boolean dropTable(String tableName) {
        String dropSQL = String.format("BEGIN EXECUTE IMMEDIATE 'DROP TABLE %s';" +
                "EXCEPTION WHEN OTHERS THEN NULL;END;", tableName);
        //删除临时表
        /*String tmpName = tableName;
        String dropTmpSQL = String.format("BEGIN EXECUTE IMMEDIATE 'DROP TABLE %s';" +
                "EXCEPTION WHEN OTHERS THEN NULL;END;", tmpName);*/
        //return jdbcUtil.executeBatch(dropSQL, dropTmpSQL);
        return jdbcUtil.executeBatch(dropSQL);
    }


    /**
     * 初始化表
     * 在表定义中插入新建表定义信息
     * 在表详细信息中插入表详细信息
     *
     * @param tableDesign
     * @param columnDesigns
     * @return
     * @throws SQLException
     */
    @Override
    public boolean initTable(TableDesign tableDesign, List<ColumnDesign> columnDesigns, String loginId,
                             String username) {
        List<String> commands = new ArrayList<>();
        String tableId = tableDesign.getTableId() == null ? System.currentTimeMillis() + Math.round(Math.random() * 1000000) + "" : tableDesign.getTableId();
        final SimpleDateFormat day = new SimpleDateFormat("yyyy-MM-dd");
        final SimpleDateFormat time = new SimpleDateFormat("HH:mm:ss");
        //生成表定义SQL
        commands.add(String.format("INSERT INTO TABLEDESIGN(SERIALNO, TABLENAME, TABLENCOM, CREATEUSER," +
                        "CREATEDATE, CREATETIME, MODIFYDATE, MODIFYTIME, REMARKS, UPLOADFREQUENCY, REPORTSTATUS, DATASOURCEID) " +
                        "VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                tableId, tableDesign.getTableEN(), tableDesign.getTableCN(), loginId, day.format(new Date()),
                time.format(new Date()), day.format(new Date()), time.format(new Date()), tableDesign.getRemarks(),
                tableDesign.getUploadFrequency(), tableDesign.getReportStatus(), tableDesign.getDataSourceId()));
        tableDesign.setTableId(tableId);
        //生成表详情插入SQL
        List<String> colCommands = tableDefinitionV2Dao.getColInsertSql(tableDesign, columnDesigns);
        commands.addAll(colCommands);
        return jdbcUtil.executeBatch(commands);
    }

    /**
     * @param tableName 表名
     * @return
     */
    @Override
    public boolean existsData(String tableName) {
        String existsData = String.format("SELECT COUNT(*) FROM %s", tableName);
        int countNum = jdbcUtil.executeCount(existsData);
        return countNum > 0;
    }

    @Override
    public List<TableDesign> findTableDesignByPage(int rows, int page, String tableEn, String tableCn) {
        StringBuffer sql = new StringBuffer("SELECT * FROM (" +
                "SELECT t.*, ROWNUM AS rowno FROM (" +
                "SELECT * FROM TABLEDESIGN ");

        // 如果表英文名和中文名都填了的话
        if (!StringUtils.isEmpty(tableEn) && !StringUtils.isEmpty(tableCn)) {
            sql.append("WHERE TABLENAME like ? and TABLENCOM like ? ");
        }

        if (StringUtils.isEmpty(tableEn) && !StringUtils.isEmpty(tableCn)) {
            sql.append("WHERE TABLENCOM like ? ");
        }

        if (!StringUtils.isEmpty(tableEn) && StringUtils.isEmpty(tableCn)) {
            sql.append("WHERE TABLENAME like ? ");
        }

        sql.append("ORDER BY MODIFYDATE,MODIFYTIME DESC) t WHERE ROWNUM <= ?" + ") WHERE rowno >= ?");

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql.toString());

            if (!StringUtils.isEmpty(tableEn) && !StringUtils.isEmpty(tableCn)) {
                ps.setString(1, "%" + tableEn + "%");
                ps.setString(2, "%" + tableCn + "%");
                ps.setInt(3, (page * rows));
                ps.setInt(4, (page - 1) * rows + 1);
            }

            if (StringUtils.isEmpty(tableEn) && !StringUtils.isEmpty(tableCn)) {
                ps.setString(1, "%" + tableCn + "%");
                ps.setInt(2, (page * rows));
                ps.setInt(3, (page - 1) * rows + 1);
            }

            if (!StringUtils.isEmpty(tableEn) && StringUtils.isEmpty(tableCn)) {
                ps.setString(1, "%" + tableEn + "%");
                ps.setInt(2, (page * rows));
                ps.setInt(3, (page - 1) * rows + 1);
            }

            if (StringUtils.isEmpty(tableEn) && StringUtils.isEmpty(tableCn)) {
                ps.setInt(1, (page * rows));
                ps.setInt(2, (page - 1) * rows + 1);
            }

            rs = ps.executeQuery();
            List<TableDesign> data = new ArrayList<>();
            while (rs.next()) {
                data.add(TableDesign.builder()
                        .tableId(rs.getString("SERIALNO"))
                        .tableEN(rs.getString("TABLENAME"))
                        .tableCN(rs.getString("TABLENCOM"))
                        .loginId(rs.getString("CREATEUSER"))
                        .uploadFrequency(rs.getString("UPLOADFREQUENCY"))
                        .reportStatus(rs.getString("REPORTSTATUS"))
                        .remarks(rs.getString("REMARKS"))
                        .dataSourceId(rs.getString("DATASOURCEID"))
                        .build());

            }
            return data;
        } catch (SQLException e) {
            LOG.error("SQL异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (ps != null) {
                    ps.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("SQL异常", e);
            }
        }
        return null;
    }

    @Override
    public int findTotal() {
        String command = "SELECT COUNT(*) FROM TABLEDESIGN";
        return jdbcUtil.executeCount(command);
    }

    @Override
    public boolean check(String checkSQL) {
        return jdbcUtil.execute(checkSQL);
    }

    @Override
    public boolean updateCheckFile(String absolutePath, long lastModified, String tableId) {
        String updateFileSQL = String.format("UPDATE TABLEDESIGN SET CHECKFILE = '%s', " +
                "LASTMODIFY = %d WHERE SERIALNO = '%s'", absolutePath, lastModified, tableId);
        return jdbcUtil.execute(updateFileSQL);
    }

    @Override
    public TableDesign findOne(String tableId) {
        String sql = "SELECT * FROM TABLEDESIGN WHERE SERIALNO = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDesign tableDesign = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableId);
            rs = ps.executeQuery();
            if (rs.next()) {
                tableDesign = TableDesign.builder()
                        .tableId(rs.getString("SERIALNO"))
                        .tableEN(rs.getString("TABLENAME"))
                        .tableCN(rs.getString("TABLENCOM"))
                        .loginId(rs.getString("CREATEUSER"))
                        .uploadFrequency(rs.getString("UPLOADFREQUENCY"))
                        .reportStatus(rs.getString("REPORTSTATUS"))
                        .remarks(rs.getString("REMARKS"))
                        .dataSourceId(rs.getString("DATASOURCEID"))
                        .build();
            }
        } catch (SQLException e) {
            LOG.error("SQL异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (ps != null) {
                    ps.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("SQL异常", e);
            }
        }
        return tableDesign;
    }
}
